package com.example.excelcompare.controller;

import com.example.excelcompare.entity.*;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.CellType;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.stereotype.Controller;
import org.springframework.ui.Model;
import org.springframework.web.bind.annotation.*;
import org.springframework.web.multipart.MultipartFile;

import javax.servlet.http.HttpServletResponse;
import java.io.IOException;
import java.text.Collator;
import java.util.ArrayList;
import java.util.Collections;
import java.util.Comparator;
import java.util.Locale;

@Controller
public class CompareExcel {

    @RequestMapping(value = "/index",method = RequestMethod.GET)
    public String index(){
        return "index";
    }

    @RequestMapping(value = "/picture",method = RequestMethod.GET)
    public String picture(){
        return "template";
    }

    @RequestMapping(value = "/excel",method = RequestMethod.POST)
    public String compareExcel(@RequestParam("file1") MultipartFile file1, @RequestParam("file2") MultipartFile file2, Model model) throws IOException {

        if (file1.isEmpty() || file2.isEmpty()){
            String message = "文件不能为空！";
            model.addAttribute("message",message);
            return "index";
        }

        String name1 = file1.getOriginalFilename();
        String name2 = file2.getOriginalFilename();
        if (name1.length()<6|| !name1.substring(name1.length()-5).equals(".xlsx") || name2.length()<6|| !name2.substring(name2.length()-5).equals(".xlsx")){
            String message = "文件类型有误，请检查后重新上传！";
            model.addAttribute("message",message);
            return "index";
        }

        //读取前一天的excel文件的sheet1页面数据
        XSSFWorkbook oldExcel = new XSSFWorkbook(file1.getInputStream());
        Sheet sht0 = oldExcel.getSheetAt(0);
        //读取当天的excel文件的sheet1页面数据
        XSSFWorkbook newExcel = new XSSFWorkbook(file2.getInputStream());
        Sheet sht1 = newExcel.getSheetAt(0);

        //对将前一天的excel数据进行保存
        ArrayList<SExcel> oldList = new ArrayList<>();
        for (int i = 1; i < sht0.getPhysicalNumberOfRows(); i++) {
            Row row = sht0.getRow(i);//获取行，第一行为表头默认从第二行开始
            SExcel excel1 = new SExcel();
            row.getCell(2).setCellType(CellType.STRING);
            excel1.setName(row.getCell(2).getStringCellValue());
            row.getCell(6).setCellType(CellType.STRING);
            excel1.setUserId(row.getCell(6).getStringCellValue());
            row.getCell(7).setCellType(CellType.STRING);
            excel1.setFinish(row.getCell(7).getStringCellValue());
            row.getCell(8).setCellType(CellType.STRING);
            excel1.setNumber(Long.valueOf(row.getCell(8).getStringCellValue()));
            row.getCell(9).setCellType(CellType.STRING);
            excel1.setHealth(row.getCell(9).getStringCellValue());
            row.getCell(10).setCellType(CellType.STRING);
            excel1.setCity(row.getCell(10).getStringCellValue());
            row.getCell(11).setCellType(CellType.STRING);
            excel1.setAddress(row.getCell(11).getStringCellValue());
            row.getCell(14).setCellType(CellType.STRING);
            excel1.setToday(row.getCell(14).getStringCellValue());
            row.getCell(15).setCellType(CellType.STRING);
            excel1.setTomorrow(row.getCell(15).getStringCellValue());
            row.getCell(16).setCellType(CellType.STRING);
            excel1.setPlan(row.getCell(16).getStringCellValue());
            row.getCell(17).setCellType(CellType.STRING);
            excel1.setHome(row.getCell(17).getStringCellValue());
            row.getCell(18).setCellType(CellType.STRING);
            excel1.setXxdz(row.getCell(18).getStringCellValue());

            oldList.add(excel1);
        }

        //对将当天的excel数据进行保存
        ArrayList<SExcel> newList = new ArrayList<>();
        for (int i = 1; i < sht1.getPhysicalNumberOfRows(); i++) {
            Row row = sht1.getRow(i);//获取行，第一行为表头默认从第二行开始
            SExcel excel2 = new SExcel();
            row.getCell(2).setCellType(CellType.STRING);
            excel2.setName(row.getCell(2).getStringCellValue());
            row.getCell(6).setCellType(CellType.STRING);
            excel2.setUserId(row.getCell(6).getStringCellValue());
            row.getCell(7).setCellType(CellType.STRING);
            excel2.setFinish(row.getCell(7).getStringCellValue());
            row.getCell(8).setCellType(CellType.STRING);
            excel2.setNumber(Long.valueOf(row.getCell(8).getStringCellValue()));
            row.getCell(9).setCellType(CellType.STRING);
            excel2.setHealth(row.getCell(9).getStringCellValue());
            row.getCell(10).setCellType(CellType.STRING);
            excel2.setCity(row.getCell(10).getStringCellValue());
            row.getCell(11).setCellType(CellType.STRING);
            excel2.setAddress(row.getCell(11).getStringCellValue());
            row.getCell(14).setCellType(CellType.STRING);
            excel2.setToday(row.getCell(14).getStringCellValue());
            row.getCell(15).setCellType(CellType.STRING);
            excel2.setTomorrow(row.getCell(15).getStringCellValue());
            row.getCell(16).setCellType(CellType.STRING);
            excel2.setPlan(row.getCell(16).getStringCellValue());
            row.getCell(17).setCellType(CellType.STRING);
            excel2.setHome(row.getCell(17).getStringCellValue());
            row.getCell(18).setCellType(CellType.STRING);
            excel2.setXxdz(row.getCell(18).getStringCellValue());

            newList.add(excel2);
        }

        //关闭文件流释放空间
        oldExcel.close();
        newExcel.close();

        SExcelVO excelVO = new SExcelVO();
        //将两天的数据进行比对
        ArrayList<Health> healthList = new ArrayList<>();
        ArrayList<SExcel> endExcel = new ArrayList<>();
        ArrayList<AddChange> addChanges = new ArrayList<>();
        ArrayList<UnSchool> schools = new ArrayList<>();
        ArrayList<HavePlan> havePlans = new ArrayList<>();
        int x = 0;//不在学校的人数
        int y = 0;//变动的人数
        for (int i = 0; i < oldList.size(); i++) {
            SExcel sExcel = oldList.get(i);
            SExcel sExcel1 = newList.get(i);
            //分别取昨天和今天表中详细地址的**省**市**区做对比
            String a = sExcel.getXxdz();
            String b = sExcel1.getXxdz();
            int index = a.indexOf(",",a.indexOf(",",a.indexOf(",")+1)+1)+2;
            String old = "该同学地址获取异常";
            if (a.length()-index > 9){
                old = a.substring(index, index+9);
            }
            int index1 = b.indexOf(",",b.indexOf(",",b.indexOf(",")+1)+1)+2;
            String today = "该同学地址获取异常";
            if (b.length()-index1 > 9){
                today = b.substring(index1, index1+9);
            }
            //是否健康
            if (!sExcel1.getHealth().equals("健康")){
                Health health = new Health();
                health.setHealth(sExcel1.getHealth());
                health.setName(sExcel1.getName());
                healthList.add(health);
            }
            model.addAttribute("healthList",healthList);
            //按照今天位置变动、明天位置变动、详细地址作为对比依据，如果有不同的则导出
            if (sExcel1.getToday().equals("是") || !old.equals(today)) {
                endExcel.add(newList.get(i));
                AddChange addChange = new AddChange();
                y += 1;
                excelVO.setChangeNum(y);
                addChange.setName(sExcel1.getName());
                addChange.setOld(old);
                addChange.setToday(today);
                addChange.setPlan(sExcel1.getPlan());
                addChanges.add(addChange);
            }
            //明日是否有出行计划
            if (!sExcel1.getPlan().equals("否")){
                HavePlan havePlan = new HavePlan();
                havePlan.setName(sExcel1.getName());
                havePlan.setOld(old);
                havePlan.setToday(today);
                havePlan.setPlan(sExcel1.getPlan());
                havePlans.add(havePlan);
            }
            if (sExcel1.getHome().equals("否")){
                x += 1;
                excelVO.setUnSchoolNum(x);
                UnSchool unSchool = new UnSchool();
                unSchool.setName(sExcel1.getName());
                unSchool.setAddress(today);
                schools.add(unSchool);
            }
        }
        //按照地址进行排序操作
        schools.sort(Comparator.comparing(UnSchool::getAddress));
        excelVO.setUnSchools(schools);
        excelVO.setAddChanges(addChanges);
        excelVO.setSExcels(endExcel);
        model.addAttribute("havePlans",havePlans);
        model.addAttribute("addChanges",addChanges);
        model.addAttribute("endExcel",endExcel);
        model.addAttribute("schools",schools);
        model.addAttribute("excelVO",excelVO);
        //System.out.println("变动人员总数为："+endExcel.size());
        //System.out.println("不在学校的人数为："+hashMap.size());
        //System.out.println(hashMap);
        return "result";
    }

    @RequestMapping(value = "template",method = RequestMethod.GET)
    public void template(HttpServletResponse response){
        // 创建excel
        HSSFWorkbook wk = new HSSFWorkbook();
        // 创建一张工作表
        HSSFSheet sheet = wk.createSheet();
        // 2
        sheet.setColumnWidth(0, 5000);
        HSSFRow row = sheet.createRow(0);
        // 创建第一行的第一个单元格
        // 想单元格写值
        HSSFCell cell = row.createCell((short) 0);
        cell.setCellValue("提交人");
        cell = row.createCell((short)1);
        cell.setCellValue("提交时间");
        cell = row.createCell((short)2);
        cell.setCellValue("学生姓名");
        cell = row.createCell((short)3);
        cell.setCellValue("填写周期");
        cell = row.createCell((short)4);
        cell.setCellValue("星期");
        cell = row.createCell((short)5);
        cell.setCellValue("userId");
        cell = row.createCell((short)6);
        cell.setCellValue("是否填写");
        cell = row.createCell((short)7);
        cell.setCellValue("学号");
        cell = row.createCell((short)8);
        cell.setCellValue("目前健康状况");
        cell = row.createCell((short)9);
        cell.setCellValue("当前所在城市");
        cell = row.createCell((short)10);
        cell.setCellValue("当日所在位置");
        cell = row.createCell((short)11);
        cell.setCellValue("目前健康状况");
        cell = row.createCell((short)12);
        cell.setCellValue("白天体温");
        cell = row.createCell((short)13);
        cell.setCellValue("晚上体温");
        cell = row.createCell((short)14);
        cell.setCellValue("今日所在位置是否有变化");
        cell = row.createCell((short)15);
        cell.setCellValue("明日所在位置是否有变化");
        cell = row.createCell((short)16);
        cell.setCellValue("明日是否有出行计划");
        cell = row.createCell((short)17);
        cell.setCellValue("当前是否在校");
        cell = row.createCell((short)18);
        cell.setCellValue("当前时间,当前地点");

        try {
            /**
             * 弹出下载选择路径框
             */
            response.setContentType("application/octet-stream");
            response.setHeader("Content-disposition", "attachment;filename=template.xls");//默认Excel名称
            response.flushBuffer();
            wk.write(response.getOutputStream());
//            wk.write(new FileOutputStream(new File("D://daochu/a.xls")));
            wk.close();
        } catch (IOException e) {
            e.printStackTrace();
        }
        finally {

        }
    }
}

